package com.jsmtr.rms.service.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jsmtr.rms.entity.*;
import com.jsmtr.rms.entity.vo.JobVoDepartment;
import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.service.ISysAdminService;
import com.jsmtr.rms.utils.StringTools;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

@Service
public class SysAdminService implements ISysAdminService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    //岗位分页查询
    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> selectBaseJobs(int page, int perpage, String code,
                                              String name, String deptcode) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("select new com.jsmtr.rms.entity.vo.JobVoDepartment(job.id, job.code,job.name,dept.name, dept.code, job.type) from BaseJob job,BaseDepartment dept where dept.code=job.deptcode ");
        StringBuffer countHQL = new StringBuffer("select count(*) from BaseJob job,BaseDepartment dept where dept.code=job.deptcode ");
        StringBuffer condition = new StringBuffer();

        if (StringTools.trimNotEmpty(code)) {
            condition.append("and job.code like '%" + code + "%' ");
        }
        if (StringTools.trimNotEmpty(name)) {
            condition.append("and job.name like '%" + name + "%' ");
        }
        if (StringTools.trimNotEmpty(deptcode)) {
            condition.append("and job.deptcode='" + deptcode + "' ");
        }
        condition.append("order by job.code");

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString())).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<JobVoDepartment> list = dao.findLimit(queryHQL.toString(), perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }
    @Override
    public Map<String, Object> getAllJobs(int page, int perpage) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("from BaseJob usr where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from BaseJob usr where 1=1 ");
        StringBuffer condition = new StringBuffer();

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString())).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<BaseUser> list = dao.findLimit(queryHQL.toString(), perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    //部门分页查询
    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> selectBaseDepartments(int page, int perpage, String code,
                                                     String name, String fullname) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("from BaseDepartment dept where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from BaseDepartment dept where 1=1 ");
        StringBuffer condition = new StringBuffer();

        if (StringTools.trimNotEmpty(code)) {
            condition.append("and dept.code like '%" + code + "%' ");
        }
        if (StringTools.trimNotEmpty(name)) {
            condition.append("and dept.name like '%" + name + "%' ");
        }
        if (StringTools.trimNotEmpty(fullname)) {
            condition.append("and dept.fullname like '%" + fullname + "%' ");
        }
        condition.append("order by dept.code");

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString())).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<BaseDepartment> list = dao.findLimit(queryHQL.toString(), perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    @Override
    public BaseJob getjobbycode(String jobcode) throws Exception {
        return (BaseJob) dao.findUnique("from BaseJob where code='" + jobcode + "'");
    }

    @Override
    public BaseJob getJobById(Integer id) throws Exception {
        return (BaseJob) dao.findUnique("from BaseJob where id='" + id + "'");
    }

    @Override
    public BaseJob getjobByName(String jobName) throws Exception {
        return (BaseJob) dao.findUnique("from BaseJob where name='" + jobName + "'");
    }

    @Override
    public List<BaseData> getPoliticals() throws Exception {
        return dao.find("from BaseData where tname='政治面貌'");
    }

    //用户分页查询
    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> selectBaseUsers(int page, int perpage, String code, String name, String deptcode) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("from BaseUser usr where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from BaseUser usr where 1=1 ");
        StringBuffer condition = new StringBuffer();

        if (StringTools.trimNotEmpty(code)) {
            condition.append("and usr.ucode like '%" + code + "%' ");
        }
        if (StringTools.trimNotEmpty(name)) {
            condition.append("and usr.uname like '%" + name + "%' ");
        }
        if (StringTools.trimNotEmpty(deptcode)) {
            condition.append("and usr.udept = '" + deptcode + "' ");
        }
        condition.append("order by usr.ucode");

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString())).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<BaseUser> list = dao.findLimit(queryHQL.toString(), perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    //获得单个用户
    @Override
    public BaseUser getUser(String ucode) throws Exception {
        return (BaseUser) dao.findUnique("from BaseUser where ucode='" + ucode + "'");
    }

    @Override
    public BaseUser getUserById(String id) throws Exception {
        return (BaseUser) dao.findUnique("from BaseUser where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseUser> getUsers(String[] ids) throws Exception {
        return dao.find("from BaseUser where id in(:idlist)", ids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseUserRole> getBaseUserRoles(String uid) throws Exception {
        return dao.find("from BaseUserRole where userid='" + uid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseUserRole> getUserRoles(String rid) throws Exception {
        return dao.find("from BaseUserRole where roleid='" + rid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> getBaseUserRole(String uid) throws Exception {
        StringBuilder sql = new StringBuilder("select r.rname from Base_Role r,Base_UserRole u ");
        sql.append("where r.id=u.roleid and u.userid='" + uid + "'");
        return dao.executeSQLQuery(sql.toString());
    }

    //角色分页查询
    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> selectBaseRoles(int page, int perpage, String rname) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("from BaseRole where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from BaseRole where 1=1 ");
        StringBuffer condition = new StringBuffer();

        if (StringTools.trimNotEmpty(rname)) {
            condition.append("and rname like '%" + rname + "%' ");
        }
        condition.append("order by id desc");

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString())).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<BaseUser> list = dao.findLimit(queryHQL.toString(), perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> selectBaseRoleUsers(int page, int perpage, String id, String code, String name, String deptcode) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("select new com.jsmtr.rms.entity.vo.RoleVoUser(role.id,user.ucode,user.uname,user.utel,user.udeptname,user.ustate) from BaseUser user,BaseUserRole role where role.userid=user.id ");
        StringBuffer countHQL = new StringBuffer("select count(*) from BaseUser user,BaseUserRole role where role.userid=user.id ");
        StringBuffer condition = new StringBuffer("and role.id='" + id + "' ");

        if (StringTools.trimNotEmpty(code)) {
            condition.append("and user.ucode like '%" + code + "%' ");
        }
        if (StringTools.trimNotEmpty(name)) {
            condition.append("and user.uname like '%" + name + "%' ");
        }
        if (StringTools.trimNotEmpty(deptcode)) {
            condition.append("and user.udept='" + deptcode + "' ");
        }
        condition.append("order by user.ucode");

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString())).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<JobVoDepartment> list = dao.findLimit(queryHQL.toString(), perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    @Override
    public BaseRole getRole(String id) throws Exception {
        return (BaseRole) dao.findUnique("from BaseRole where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseRole> getRoles() throws Exception {
        return dao.find("from BaseRole");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseRole> getRoles(String[] ids) throws Exception {
        return dao.find("from BaseRole where id in(:idlist)", ids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseModular> getBaseModulars(int parentid) throws Exception {
        return dao.find("from BaseModular where parentid='" + parentid + "' order by morder");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseModular> getBaseModulars(String rid) throws Exception {
        return dao.find("from BaseModular where id in(select mid from BaseRoleModular where rid='" + rid + "') order by morder");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> getBaseModularFunctions(String rid) throws Exception {
        StringBuilder sql = new StringBuilder("select f.murl from Base_ModularFunction f,Base_Modular m,Base_RoleModular r ");
        sql.append("where f.mid=m.id and m.id= r.mid and r.rid='" + rid + "'");
        return dao.executeSQLQuery(sql.toString());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BaseRoleModular> getBaseRoleModulars(String rid) throws Exception {
        return dao.find("from BaseRoleModular where rid='" + rid + "'");
    }
    @Override
    public List<ProjectInfo> getProjectInfo() throws Exception{
        return dao.find("from ProjectInfo");
    }

    @Override
    @SuppressWarnings("rawtypes")
    public List getCount(String field, String year) throws Exception {
        String sql = "select distinct ur." + field +", count(*) over ( partition by ur." + field + ", to_char(ur.adddate, 'yyyy') order by ur." + field + ") as cnt\n" +
                "from DUSERRESUME ur\n" +
                "where " + field + " is not null and to_char(ur.adddate, 'yyyy')=" + year;
        return  dao.executeSQLQuery(sql);
    }

    @Override
    public List getMsCount(String year)  throws Exception {
        String sql = "select distinct result, count(*) over ( partition by result, substr(starttime, 0, 4) order by result) as cnt\n" +
                "from DOMS_USER\n" +
                "where  substr(starttime, 0, 4)=" + year;
        return dao.executeSQLQuery(sql);
    }

    @Override
    public List getBsCount(String year) throws Exception {
        String sql = "select distinct result, count(*) over ( partition by result, substr(starttime, 0, 4) order by result) as cnt\n" +
                "from DOBS_USER\n" +
                "where  substr(starttime, 0, 4)=" + year;
        return dao.executeSQLQuery(sql);
    }

    @Override
    public List getUserByUcode(String ucode) throws Exception {
        String hql = "select u.id,u.ucode,u.uname,r.rname,r.rtype from BaseUser u , BaseUserRole ur ,BaseRole r\n" +
                "where u.id=ur.userid\n" +
                "  and ur.roleid=r.id\n" +
                "  and u.ucode=:ucode";
        return dao.find(hql, "ucode", ucode);
    }
}